package com.lagou.app;

import com.lagou.entity.Phone;
import com.lagou.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import java.sql.SQLException;
import java.util.List;

public class TestPhone {

    // 需求1: 查询价格高于2000元，生产日期是2019年之前的所有手机
    @Test
    public void findPhoneByPriceDate() throws SQLException {
        // 1.获取QueryRunner对象
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        // 2.编写sql
        String sql = "select * from phone where price > ? and prodate < ?";
        // 3.对象数组，赋值给占位符
        Object[] obj = {2000.0, "2019-01-01"};
        // 4.查询出来的Phone以集合的形式进行遍历，得到所有满足条件的对象
        List<Phone> phoneList = qr.query(sql, new BeanListHandler<>(Phone.class), obj);
        System.out.println("价格高于2000元，生产日期是2019年之前的所有手机有: ");
        for (Phone phone : phoneList) {
            System.out.println(phone);
        }
    }

    // 需求2: 查询所有颜色是白色的手机信息
    @Test
    public void findPhoneByColor() throws SQLException {
        // 1.获取连接
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        // 2.编写sql
        String sql = "select * from phone where color = ?";
        // 3.执行查询
        List<Phone> phoneList = qr.query(sql, new BeanListHandler<>(Phone.class), "白色");
        // 4.遍历结果集
        System.out.println("颜色是白色的手机信息: ");
        for (Phone phone : phoneList) {
            System.out.println(phone);
        }
    }
}
